﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.Mvc;
using log4net;
using TheBullysStore.Models;
using TheBullysStore.Models.ModuloAdministracion.Login;

namespace TheBullysStore.Models.ModuloAdministracion.Login
{
    public class LoginModelDAO
    {
        private static ILog log = LogManager.GetLogger(typeof(LoginModelDAO));
        public static String cadenaDB = WebConfigurationManager.ConnectionStrings["TheBullysStore"].ConnectionString;       

        public static bool comprobarLogin(LoginModel login) {
            SqlConnection objDB = null;
            try
            {
                objDB = new SqlConnection(cadenaDB);

                objDB.Open();
                String strQuery = "SELECT * FROM Persona WHERE usuario = '" + login.usuario + "' AND password = '" + login.password + "' AND estado = 1";
                SqlCommand ObjQuery = new SqlCommand(strQuery, objDB);

                SqlDataReader objReader = ObjQuery.ExecuteReader();
                
                return objReader.HasRows;
            }
            catch (Exception ex)
            {
                log.Error("comprobarLogin(EXCEPTION): ", ex);
                throw ex;
            }
            finally { 
                objDB.Close();
            }
        }

        public List<int> devolverCargo(LoginModel login)
        {
            SqlConnection sqlCon = null;

            try
            {
                List<int> listaCargo = new List<int>();

                int cargo;

                sqlCon = new SqlConnection(cadenaDB);

                sqlCon.Open();

                string commandString = "SELECT * FROM Persona WHERE usuario = '" + login.usuario + "' AND password = '" + login.password + " AND estado = 1";

                SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

                SqlDataReader dataReader = sqlCmd.ExecuteReader();

                while (dataReader.Read())
                {
                    cargo = Convert.ToInt32(dataReader["Id_Cargo"]);
                    listaCargo.Add(cargo);
                }
                dataReader.Close();

                return listaCargo;
            }
            catch (Exception ex)
            {
                log.Error("devolverCargo(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sqlCon.Close();
            }
        }

        public LoginModel getLogin(LoginModel login)
        {
            SqlConnection sql = null;

            try
            {
                sql = new SqlConnection(cadenaDB);

                sql.Open();

                String command = "SELECT * FROM Persona WHERE usuario = '" + login.usuario + "' AND password = '" + login.password + "' AND estado > 0";

                SqlCommand query = new SqlCommand(command, sql);


                SqlDataReader data = query.ExecuteReader();

                LoginModel usuarioBean = null;

                if (data.HasRows)
                {
                    data.Read();
                    string usuario = Convert.ToString(data["Usuario"]);

                    usuarioBean = this.getUsuario(usuario);
                    
                }

                data.Close();

                return usuarioBean;
            }
            catch (Exception ex)
            {
                log.Error("getLogin(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sql.Close();
            }
        }

        public LoginModel getUsuario(string usuario)
        {
            SqlConnection sql = null;

            try
            {
                sql = new SqlConnection(cadenaDB);

                sql.Open();

                String command = "Select * from Persona where usuario = '" + usuario + "'";

                SqlCommand query = new SqlCommand(command, sql);

                SqlDataReader data = query.ExecuteReader();

                LoginModel usuarioBean = null;

                if (data.HasRows)
                {
                    data.Read();

                    usuarioBean = new LoginModel();

                    usuarioBean.usuario = Convert.ToString(data["Usuario"]);
                    usuarioBean.password = Convert.ToString(data["Password"]);
                    usuarioBean.cargo = Convert.ToInt32(data["Id_Cargo"]);
                    usuarioBean.edificio = Convert.ToInt32(data["Id_Edificio"]);
                    usuarioBean.permiso = this.getPermiso(usuarioBean.cargo);
                    usuarioBean.categoria = this.getCategoria(usuarioBean.cargo);
                    string nombre = Convert.ToString(data["Nombres"]);
                    string apellido = Convert.ToString(data["Apellidos"]);
                    usuarioBean.nombre = nombre + " " + apellido;
                    usuarioBean.idPersona = Convert.ToInt32(data["Id_Persona"]);
                    usuarioBean.dirLogo = this.getDirLogo(usuarioBean.edificio);
                    usuarioBean.juntaDirectiva = this.getJuntaDirectiva(usuarioBean.idPersona);

                }

                data.Close();

                return usuarioBean;
            }
            catch (Exception ex)
            {
                log.Error("getUsuario(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sql.Close();
            }
        }

        public int getJuntaDirectiva(int IdPersona)
        {
            SqlConnection sql = null;
            try
            {
                sql = new SqlConnection(cadenaDB);

                sql.Open();

                String command = "SELECT * FROM junta_x_propietario WHERE id_persona = " + IdPersona + "";

                SqlCommand query = new SqlCommand(command, sql);
                SqlDataReader data = query.ExecuteReader();

                int junta = 0;

                if (data.HasRows)
                {
                    data.Read();

                    junta = Convert.ToInt32(data["Id_Junta_Propietario"]);
                }

                sql.Close();
                data.Close();

                return junta;
            }
            catch (Exception ex)
            {
                log.Error("getPermiso(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sql.Close();
            }
        }

        public string getDirLogo(int IdEdificio)
        {
            SqlConnection sql = null;
            try
            {
                sql = new SqlConnection(cadenaDB);

                sql.Open();

                String command = "SELECT * FROM Edificio WHERE id_Edificio = " + IdEdificio + "";

                SqlCommand query = new SqlCommand(command, sql);
                SqlDataReader data = query.ExecuteReader();

                string DirLogo="";

                if (data.HasRows) {
                    data.Read();

                    DirLogo = Convert.ToString(data["DirLogo"]);
                }

                sql.Close();
                data.Close();

                return DirLogo;
            }
            catch (Exception ex)
            {
                log.Error("getPermiso(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sql.Close();
            }
        }

        public string getCategoria(int cargo)
        {
            SqlConnection sql = null;
            try
            {
                sql = new SqlConnection(cadenaDB);

                sql.Open();

                string categoria = null;

                String command = "SELECT * FROM Cargo WHERE id_Cargo = " + cargo + "";

                SqlCommand query = new SqlCommand(command, sql);
                SqlDataReader data = query.ExecuteReader();

                if (data.HasRows) {
                    data.Read();
                    categoria = Convert.ToString(data["Categoria"]);
                }

                sql.Close();
                data.Close();

                return categoria;
            }
            catch (Exception ex)
            {
                log.Error("getCategoria(EXCEPTION): ", ex);
                throw ex;
            }
            finally
            {
                sql.Close();
            }
        }

        public List<int> getPermiso(int cargo)
        {
            SqlConnection sql = null;
            try
            {
                sql = new SqlConnection(cadenaDB);

                sql.Open();

                String command = "SELECT * FROM Cargo_x_Permiso WHERE id_Cargo = " + cargo + "";

                SqlCommand query = new SqlCommand(command, sql);
                SqlDataReader data = query.ExecuteReader();

                List<int> listaPermisos = new List<int>();

                while (data.Read()) {
                    int permiso;

                    permiso = Convert.ToInt32(data["Id_Permiso"]);

                    listaPermisos.Add(permiso);
                
                }

                sql.Close();
                data.Close();

                return listaPermisos;
            }
            catch (Exception ex)
            {
                log.Error("getPermiso(EXCEPTION): ", ex);
                throw ex;
            }
            finally {
                sql.Close();
            }
        }
    }
}